/*
 * Created on 16/08/2013 by halyson.goncalves
 * 
 * Copyright GONÇALVES
 */
package br.com.halyson.pontoeletronico.dao;

import java.io.File;

import br.com.halyson.pontoeletronico.R;
import br.com.halyson.pontoeletronico.model.Nota;
import br.com.halyson.pontoeletronico.model.Ponto;
import br.com.halyson.pontoeletronico.model.PontoDia;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.os.Environment;
import android.util.Log;
import android.widget.Toast;

public class DataHelper extends SQLiteOpenHelper
{
    // DB
    private static final String TAG = "DataHelper";
    
    public static final String DATABASE_NAME = "pontoEletronico.db";
    
    private static final int DATABASE_VERSION = 1;
    
    private SQLiteDatabase db;
    
    // Tabelas
    private static final String TABLE_PONTO = "ponto";
    
    private static final String TABLE_MESES = "meses";
    
    private static final String TABLE_OBSERVACAO = "observacao";
    
    // Variáveis DB
    public static final String ID_MES = "id_mes";
    
    public static final String ID_PONTO = "id_ponto";
    
    public static final String MES = "mes";
    
    public static final String ANO = "ano";
    
    public static final String NOTA = "nota";
    
    public static final String DATA = "data";
    
    public static final String DIA = "dia";
    
    public static final String HORA = "hora";
    
    public static final String STATUS = "status";
    
    // Query
    private static final String INSERT_PONTO = "insert into " + TABLE_PONTO + "(" + ID_MES + "," + DATA + "," + DIA + "," + HORA
            + "," + STATUS + ") values (?,?,?,?,?)";
    
    private static final String INSERT_OBSERVACAO = "insert into " + TABLE_OBSERVACAO + "(" + DATA + "," + NOTA
            + ") values (?,?)";
    
    private static final String INSERT_MESES = "insert into " + TABLE_MESES + "(" + MES + "," + ANO + ") values (?,?)";
    
    private String[] allColumnsPonto = { "id", ID_MES, DATA, DIA, HORA, STATUS };
    
    private String[] allColumnsMes = { "id", MES, ANO };
    
    private String[] allColumnsObservacao = { "id", DATA, NOTA };
    
    public DataHelper(Context context)
    {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    
    private void open() throws SQLException
    {
        db = getWritableDatabase();
    }
    
    public void close()
    {
        db.close();
    }
    
    @Override
    public void onCreate(SQLiteDatabase db)
    {
        db.execSQL("CREATE TABLE " + TABLE_PONTO + "(id INTEGER PRIMARY KEY AUTOINCREMENT, " + ID_MES + " INTEGER, " + DATA
                + " TEXT NOT NULL,  " + DIA + " TEXT NOT NULL,  " + HORA + " TEXT NOT NULL,  " + STATUS
                + " TEXT NOT NULL, FOREIGN KEY (" + ID_MES + ") REFERENCES " + TABLE_MESES + "(id))");
        
        db.execSQL("CREATE TABLE " + TABLE_MESES + "(id INTEGER PRIMARY KEY AUTOINCREMENT, " + MES + " TEXT NOT NULL, " + ANO
                + " TEXT NOT NULL, " + "UNIQUE (" + MES + ") ON CONFLICT REPLACE)");
        
        db.execSQL("CREATE TABLE " + TABLE_OBSERVACAO + "(id INTEGER PRIMARY KEY AUTOINCREMENT, " + DATA + " TEXT NOT NULL,  "
                + NOTA + " TEXT NOT NULL)");
        
    }
    
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        Log.w(TAG, "Upgrading database, this will drop tables and recreate.");
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PONTO);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_MESES);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_OBSERVACAO);
        
        onCreate(db);
    }
    
    public long insertPonto(Ponto ponto)
    {
        
        long mes = selectMesDB(ponto.getMesExtensive());
        if (mes < 0)
        {
            mes = insertMes(ponto.getMesExtensive(), ponto.getAno());
        }
        
        if (mes >= 0)
        {
            open();
            
            SQLiteStatement insertStmt = db.compileStatement(INSERT_PONTO);
            insertStmt.bindLong(1, mes);
            insertStmt.bindString(2, ponto.getDia() + "." + ponto.getMes() + "." + ponto.getAno());
            insertStmt.bindString(3, ponto.getDayExtensive());
            insertStmt.bindString(4, ponto.getHora());
            insertStmt.bindString(5, ponto.getStatus());
            
            long retorno = insertStmt.executeInsert();
            close();
            return retorno;
        }
        return mes;
    }
    
    public long insertNota(Nota nota)
    {
        open();
        
        long retorno = -1;
        long mes = selectMesDB(nota.getMesExtensive());
        if (mes < 0)
        {
            open();
            
            mes = insertMes(nota.getMesExtensive(), nota.getAno());
            
            SQLiteStatement insertStmt = db.compileStatement(INSERT_OBSERVACAO);
            insertStmt.bindString(1, nota.getData());
            insertStmt.bindString(2, nota.getNota());
            
            retorno = insertStmt.executeInsert();
            close();
            return retorno;
        }
        else if (mes >= 0)
        {
            open();
            
            SQLiteStatement insertStmt = db.compileStatement(INSERT_OBSERVACAO);
            insertStmt.bindString(1, nota.getData());
            insertStmt.bindString(2, nota.getNota());
            
            retorno = insertStmt.executeInsert();
            close();
            return retorno;
        }
        close();
        return retorno;
    }
    
    private long insertMes(String mes, String ano)
    {
        open();
        
        SQLiteStatement insertStmt = db.compileStatement(INSERT_MESES);
        insertStmt.bindString(1, mes);
        insertStmt.bindString(2, ano);
        
        long retorno = insertStmt.executeInsert();
        close();
        return retorno;
    }
    
    private long selectMesDB(String mes)
    {
        open();
        long retorno = -1;
        Cursor cursor = db.query(TABLE_MESES, allColumnsMes, MES + " = '" + mes + "'", null, null, null, null);
        if (cursor.moveToFirst())
        {
            retorno = cursor.getLong(0);
        }
        cursor.close();
        close();
        return retorno;
    }
    
    public PontoDia selectAllByDay(String data)
    {
        open();
        Cursor cursor = this.db.query(TABLE_PONTO, allColumnsPonto, DATA + " LIKE '%" + data + "%'", null, null, null, "id ASC");
        
        PontoDia pontoDia = new PontoDia();
        pontoDia = cursorToPonto(cursor);
        cursor.close();
        close();
        
        return pontoDia;
    }
    
    public Boolean selectByNoteDayIsExist(String data)
    {
        open();
        
        Cursor cursor = db.query(TABLE_OBSERVACAO, allColumnsObservacao, DATA + " = '" + data + "'", null, null, null, null);
        if (cursor.moveToFirst())
        {
            return true;
        }
        cursor.close();
        close();
        return false;
    }
    
    public String selectByNoteDay(String data)
    {
        open();
        
        Cursor cursor = db.query(TABLE_OBSERVACAO, allColumnsObservacao, DATA + " = '" + data + "'", null, null, null, null);
        if (cursor.moveToFirst())
        {
            return cursor.getString(2);
        }
        
        cursor.close();        
        close();
        return null;
    }
    
    private PontoDia cursorToPonto(Cursor cursor)
    {
        PontoDia pontoDia = new PontoDia();
        
        if (cursor.moveToFirst())
        {
            do
            {
                if (cursor.getString(5).equals("entrada"))
                {
                    pontoDia.setEntrada(true);
                }
                else if (cursor.getString(5).equals("saida_almoco"))
                {
                    pontoDia.setSaida_almoco(true);
                }
                else if (cursor.getString(5).equals("volta_almoco"))
                {
                    pontoDia.setVolta_almoco(true);
                }
                else if (cursor.getString(5).equals("saida"))
                {
                    pontoDia.setSaida(true);
                }
            }
            while (cursor.moveToNext());
        }
        return pontoDia;
    }
    
}
